-- ============================================================
-- DataDrop L2 Starter Database
-- Lesson 2: Designing a Database
-- ============================================================
-- This file provides:
-- 1. The Songs table (already built — students should not modify this)
-- 2. A CREATE TABLE template for Artists (students build this themselves)
-- 3. Teacher notes on the expected Artists table design
-- ============================================================

-- SONGS TABLE (already exists in the starter file)
-- Students open DataDrop_L2_starter.accdb which has this pre-built.

CREATE TABLE Songs (
    SongID       INTEGER     NOT NULL,
    Title        TEXT(100)   NOT NULL,
    Artist       TEXT(80)    NOT NULL,
    Genre        TEXT(30)    NOT NULL,
    ReleaseYear  INTEGER     NOT NULL,
    DurationMins DOUBLE      NOT NULL,
    Streams      INTEGER     NOT NULL,
    IsExplicit   YESNO       NOT NULL,
    CONSTRAINT PK_Songs PRIMARY KEY (SongID)
);

INSERT INTO Songs VALUES (1,  'Neon Skyline',      'The Midnight',     'Synthpop',  2021, 4.2,  2340000, FALSE);
INSERT INTO Songs VALUES (2,  'Ocean Drive',        'Duke Dumont',      'Electronic',2014, 3.8,  5100000, FALSE);
INSERT INTO Songs VALUES (3,  'Bad Habits',         'Ed Sheeran',       'Pop',       2021, 3.7,  9800000, FALSE);
INSERT INTO Songs VALUES (4,  'Blinding Lights',    'The Weeknd',       'Pop',       2019, 3.4, 12000000, FALSE);
INSERT INTO Songs VALUES (5,  'Heat Waves',         'Glass Animals',    'Indie',     2020, 3.9,  8500000, FALSE);
INSERT INTO Songs VALUES (6,  'Industry Baby',      'Lil Nas X',        'Hip-Hop',   2021, 3.6,  7200000, TRUE);
INSERT INTO Songs VALUES (7,  'Levitating',         'Dua Lipa',         'Pop',       2020, 3.5,  6800000, FALSE);
INSERT INTO Songs VALUES (8,  'Stay',               'Kid Laroi',        'Pop',       2021, 2.5,  9100000, TRUE);
INSERT INTO Songs VALUES (9,  'Wellerman',          'Nathan Evans',     'Folk',      2021, 2.2,  3200000, FALSE);
INSERT INTO Songs VALUES (10, 'Good 4 U',           'Olivia Rodrigo',   'Pop-Rock',  2021, 2.9,  7600000, FALSE);

-- ============================================================
-- ARTISTS TABLE — Expected student solution
-- Students design this on paper first (worksheet Task 1)
-- then build it in Access Design View (worksheet Task 2)
-- ============================================================

-- Teacher reference — the completed Artists table should look like this:

CREATE TABLE Artists (
    ArtistID          INTEGER     NOT NULL,
    ArtistName        TEXT(80)    NOT NULL,
    Country           TEXT(50)    NOT NULL,
    Genre             TEXT(30)    NOT NULL,
    MonthlyListeners  INTEGER     NOT NULL,
    IsVerified        YESNO       NOT NULL,
    DateJoined        DATE        NOT NULL,
    CONSTRAINT PK_Artists PRIMARY KEY (ArtistID)
);

-- Validation rules to add in Access Design View:
-- Genre field: IN ("Pop","Rock","Hip-Hop","Electronic","Indie","R&B","Folk","K-Pop","Synthpop")
-- MonthlyListeners: >=0
-- Validation Text for MonthlyListeners: "Listeners cannot be negative"

-- ============================================================
-- SAMPLE DATA — students enter these 8 records to test validations
-- ============================================================

INSERT INTO Artists VALUES (1, 'Ed Sheeran',    'United Kingdom', 'Pop',        85000000, TRUE,  #2019-06-15#);
INSERT INTO Artists VALUES (2, 'Dua Lipa',       'United Kingdom', 'Pop',        68000000, TRUE,  #2020-01-10#);
INSERT INTO Artists VALUES (3, 'The Weeknd',     'Canada',         'R&B',        89000000, TRUE,  #2018-03-22#);
INSERT INTO Artists VALUES (4, 'Glass Animals',  'United Kingdom', 'Indie',      22000000, TRUE,  #2020-07-04#);
INSERT INTO Artists VALUES (5, 'Lil Nas X',      'United States',  'Hip-Hop',    37000000, TRUE,  #2019-04-05#);
INSERT INTO Artists VALUES (6, 'Olivia Rodrigo', 'United States',  'Pop',        56000000, TRUE,  #2021-01-08#);
INSERT INTO Artists VALUES (7, 'The Midnight',   'United States',  'Synthpop',   8500000,  FALSE, #2021-09-17#);
INSERT INTO Artists VALUES (8, 'Nathan Evans',   'United Kingdom', 'Folk',       4200000,  FALSE, #2021-02-01#);

-- ============================================================
-- TEACHER NOTES:
-- L2 builds on L1. Give students DataDrop_L2_starter.accdb
-- which contains the Songs table but NOT the Artists table.
-- Students must:
--   Task 1: Complete the table design grid on the worksheet (paper planning)
--   Task 2: Create the Artists table in Access Design View
--   Task 3: Add validation rules to Genre and MonthlyListeners
--   Task 4: Enter the 8 sample records above
--   Task 5: Test their validations (try entering a negative number, invalid genre)
--
-- Extension: Students can try creating a relationship between Songs and Artists
-- (requires adding ArtistID as a foreign key in the Songs table)
-- ============================================================
